package net.trombonium.steroid;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.TreeMap;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.app.AlarmManager;
import android.app.PendingIntent;
import android.content.*;
import android.database.*;

public class DBHelper extends SQLiteOpenHelper {
	private static final String DB_NAME = "steroid.db";
	private static final int DB_VERSION = 4;
	private static final String TAG = "DBHelper";
	
	private Context context;
	
	public DBHelper(Context ctx){
		super(ctx, DB_NAME, null, DB_VERSION);
		context = ctx;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		//String createSurveyTable = "CREATE TABLE surveys( id INTEGER PRIMARY KEY, name VARCHAR(64) );";
		//String createItemTable = "CREATE TABLE items( id INTEGER PRIMARY KEY, survey_id INT, prompt TEXT, returns TEXT, next TEXT );";
		//db.execSQL(createSurveyTable);
		//db.execSQL(createItemTable);
		
		String createSurveyTable = "CREATE TABLE surveys( id INTEGER PRIMARY KEY, name VARCHAR(64), run_immed INT, results_buffer INT );"; //two columns added
		String createItemTable = "CREATE TABLE items( id INTEGER PRIMARY KEY, survey_id INT, prompt TEXT, returns TEXT, next TEXT );";
		String createCronjobsTable = "CREATE TABLE cronjobs( id INTEGER PRIMARY KEY, survey_id INT, action INT, min VARCHAR(64), hour VARCHAR(64), dom VARCHAR(64), mon VARCHAR(64), dow VARCHAR(64) );"; //cronjobs table
		// dom: day of the month; mon: Month; dow:day of the week
		
		String createTouchMeTable = "CREATE TABLE rectangles( id INTEGER PRIMARY KEY, pointx INT, pointy INT, width INT, height INT, pic_width INT, pic_height INT, name VARCHAR(128), item_id INT );"; //Table for Touch Screen
		
		db.execSQL(createSurveyTable);
		db.execSQL(createItemTable);
		db.execSQL(createCronjobsTable);
		db.execSQL(createTouchMeTable);  //Create the TouchMeTable
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		switch(oldVersion){
		case 1:
			String createSurveyTable = "CREATE TABLE surveys( id INTEGER PRIMARY KEY, name VARCHAR(64) );";
			String createItemTable = "CREATE TABLE items( id INTEGER PRIMARY KEY, survey_id INT, prompt TEXT, returns TEXT, next TEXT );";
			db.execSQL("DROP TABLE surveys;");
			db.execSQL("DROP TABLE items;");
			db.execSQL(createSurveyTable);
			db.execSQL(createItemTable);
		case 2:
			String createTouchMeTable = "CREATE TABLE rectangles( id INTEGER PRIMARY KEY, pointx INT, pointy INT, width INT, height INT, name VARCHAR(128), item_id INT );";
			db.execSQL(createTouchMeTable);
		case 3:
			db.execSQL("ALTER TABLE rectangles ADD COLUMN pic_width INT;");
			db.execSQL("ALTER TABLE rectangles ADD COLUMN pic_height INT;");
			break;
		default:
			//what the heck just happened?
			Log.e(TAG, "Tried to upgrade from version "+String.valueOf(oldVersion)+" to version "+String.valueOf(newVersion)+" but oldVersion was not a case in the switch statement.");
			break;
		}
	}
	
	public boolean recordSurvey(ContentValues values, int surveyId){
		values.put("user_id", getUserId());
		values.put("time", (new Timestamp(System.currentTimeMillis())).toString());
		String tableName = "results" + String.valueOf(surveyId);
		SQLiteDatabase db = this.getWritableDatabase();
		db.insert(tableName, null, values);
		boolean rvar = timeToUpload(db, surveyId);
		db.close();
		return rvar;
	}
	
	private boolean timeToUpload(SQLiteDatabase db, int surveyId){
		String rTable = "results"+surveyId;
		Cursor cntCursor = db.rawQuery("SELECT COUNT(*) FROM "+rTable, null);
		cntCursor.moveToFirst();
		int count = cntCursor.getInt(0);
		
		Cursor bCursor = getById("surveys", surveyId, db);
		int buffer = bCursor.getInt(bCursor.getColumnIndex("results_buffer"));
		
		cntCursor.close();
		bCursor.close();
		return (count >= buffer);
	}
	
	Survey getSurvey(int surveyId){
		SQLiteDatabase db = this.getReadableDatabase();
		String idClause = "id=" + String.valueOf(surveyId);
		
		//Cursor surveyCursor = db.query("surveys", null, idClause, null, null, null, null);
		Survey s = new Survey(surveyId);
		idClause = "survey_id=" + String.valueOf(surveyId);
		Cursor itemCursor = db.query("items", null, idClause, null, null, null, null);
		while(itemCursor.moveToNext()){
			int itemId = itemCursor.getInt(itemCursor.getColumnIndex("id"));
			String prompt = itemCursor.getString(itemCursor.getColumnIndex("prompt"));
			Item i = new Item(itemId, prompt);
			i.addResponses(itemCursor.getString(itemCursor.getColumnIndex("returns")));
			i.setNexts(itemCursor.getString(itemCursor.getColumnIndex("next")));
			
			s.addItem(i);
		}
		itemCursor.close();
		db.close();
		return s;
	}
	
	public void addSurvey(String query){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL(query);
		db.close();
	}
	
	public void runSQLFiles(ArrayList<String> sqlFiles) throws IOException{
		Log.i(TAG, "runSQLFiles");
		for(int i=0; i<sqlFiles.size(); ++i){
			BufferedReader fileIn = new BufferedReader(new FileReader(new File(sqlFiles.get(i))));
			String query;
			SQLiteDatabase db = this.getWritableDatabase();
			
			while((query = fileIn.readLine()) != null){
				Log.i(TAG, query);
				db.execSQL(query);
			}
			db.close();
		}
	}
	
	private String makeInsertLine(String table, String[] cols){
		String s = "INSERT INTO " + table + "(";
		for(int i=0; i<cols.length; ++i){
			if(i!=0)
				s += ",";
			s += cols[i];
		}
		s += ") VALUES(";
		return s;
	}
	
	public String getResultsSQL(int surveyId){
		SQLiteDatabase db = this.getReadableDatabase();
		String table = "results" + String.valueOf(surveyId);
		Cursor results = db.query(table, null, null, null, null, null, null);
		String[] cols = results.getColumnNames();
		String insertLine = makeInsertLine(table, cols);
		
		String sql = "";
		while(results.moveToNext()){
			sql += insertLine;
			for(int i=0; i<cols.length; ++i){
				if(i!=0)
					sql += ",";
				sql += '"' + results.getString(results.getColumnIndex(cols[i])) + '"';
			}
			sql += ");\n";
		}
		results.close();
		db.close();
		return sql;
	}
	
	public void clearResults(int surveyId){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DELETE FROM results"+String.valueOf(surveyId));
		db.close();
	}
	
	public void parseCronjobs(String selection){
		Calendar cal = Calendar.getInstance();
		ArrayList<Integer> runNow = new ArrayList<Integer>();
		SQLiteDatabase db = this.getReadableDatabase();
		AlarmManager alarmer = (AlarmManager)context.getSystemService(Context.ALARM_SERVICE);
		Cursor cursor = db.query("cronjobs", null, selection, null, null, null, null);
		while(cursor.moveToNext()){
			if(cronjobMatch(cal, cursor)){
				int min = cursor.getInt(cursor.getColumnIndex("min"));
				if(min==cal.get(Calendar.MINUTE))
					runNow.add(new Integer(cursor.getInt(cursor.getColumnIndex("survey_id"))));
				else{
					cal.set(Calendar.MINUTE, cursor.getInt(cursor.getColumnIndex("min")));
					Intent intent = new Intent(context, Notify.class);
					intent.putExtra(Notify.S_ID, cursor.getInt(cursor.getColumnIndex("survey_id")));
					alarmer.set(AlarmManager.RTC_WAKEUP, cal.getTimeInMillis(),PendingIntent.getBroadcast(context, 0, intent, 0));
					Log.i(TAG, "Set survey alarm for "+String.valueOf(cal.get(Calendar.MINUTE)));
					//Notify.setSurveyNotification(context, cursor.getInt(cursor.getColumnIndex("survey_id")), cal.getTimeInMillis());
				}
			}
		}
		cursor.close();
		db.close();
		for(int i=0; i<runNow.size(); ++i){
			Notify.setSurveyNotification(context, runNow.get(i));
		}
	}
	
	private boolean cronjobMatch(Calendar cal, Cursor cursor){
		if(	!cursor.getString(cursor.getColumnIndex("mon")).equals("*") && 
			cursor.getInt(cursor.getColumnIndex("mon"))!=cal.get(Calendar.MONTH))
			return false;

		if(	!cursor.getString(cursor.getColumnIndex("dom")).equals("*") && 
			cursor.getInt(cursor.getColumnIndex("dom"))!=cal.get(Calendar.DAY_OF_MONTH))
			return false;

		if(	!cursor.getString(cursor.getColumnIndex("dow")).equals("*") && 
			cursor.getInt(cursor.getColumnIndex("dow"))!=cal.get(Calendar.DAY_OF_WEEK)-1)
			return false;

		if(	!cursor.getString(cursor.getColumnIndex("hour")).equals("*") && 
			cursor.getInt(cursor.getColumnIndex("hour"))!=cal.get(Calendar.HOUR_OF_DAY))
			return false;
		
		if(	cursor.getInt(cursor.getColumnIndex("min"))<cal.get(Calendar.MINUTE))
				return false;
		
		return true;
	}
	public TreeMap<Integer, String> getSurveys(){
		TreeMap<Integer, String> surveys = new TreeMap<Integer, String>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.query("surveys", null, null, null, null, null, null);
		while(c.moveToNext()){
			int id = c.getInt(c.getColumnIndex("id"));
			String name = c.getString(c.getColumnIndex("name"));
			surveys.put(new Integer(id), name);
		}
		c.close();
		db.close();
		return surveys;
	}
	
	public int getUserId(){
		//for now...
		return 1;
	}
	
	public String getRectangle(int item_id, int xtouch, int ytouch, int pic_width, int pic_height){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor recCursor = db.query("rectangles",null, "item_id=" + String.valueOf(item_id), null, null, null, null);
		
		while(recCursor.moveToNext()){
			double xScale = pic_width / (recCursor.getDouble(recCursor.getColumnIndex("pic_width")));
			double yScale = pic_height / (recCursor.getDouble(recCursor.getColumnIndex("pic_height")));
			double pointx = recCursor.getInt(recCursor.getColumnIndex("pointx")) * xScale;
			double pointy = recCursor.getInt(recCursor.getColumnIndex("pointy")) * yScale;
			double width = recCursor.getInt(recCursor.getColumnIndex("width")) * xScale;
			double height = recCursor.getInt(recCursor.getColumnIndex("height")) * yScale;
			String name = recCursor.getString(recCursor.getColumnIndex("name"));
			
			if ( xtouch > pointx && xtouch <= (pointx+width) && ytouch >pointy && ytouch <= (pointy +height) ){
				recCursor.close();
				db.close();
				return name;
			}
		}	
		recCursor.close();
		db.close();
		return "";
	}
		
	public String getSurveyName(int surveyId){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = getById("surveys", surveyId, db);
		String  rvar;
		if(cursor!=null)
			rvar = cursor.getString(cursor.getColumnIndex("name"));
		else
			rvar = "";
		cursor.close();
		db.close();
		return rvar;
	}
	
	public boolean runImmed(int surveyId){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = getById("surveys", surveyId, db);
		boolean  rvar;
		if(cursor!=null){
			int val = cursor.getInt(cursor.getColumnIndex("run_immed"));
			rvar = (val==1);	
		}
		else
			rvar = false;
		cursor.close();
		db.close();
		return rvar;
	}
	
	private Cursor getById(String table, int id, SQLiteDatabase db){
		Cursor cursor = db.query(table, null, "id="+String.valueOf(id), null, null, null, null);
		if(cursor.getCount()==0)
			return null;
		cursor.moveToFirst();
		return cursor;
	}
	
	public void deleteEverything(){
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query("surveys", null, null, null, null, null, null);
		while(cursor.moveToNext()){
			int sId = cursor.getInt(cursor.getColumnIndex("id"));
			try{
				db.execSQL("DROP TABLE results"+String.valueOf(sId));
			} catch(Exception e){
				//prb doesn't exist for some reason...whatever
			}
		}
		cursor.close();
		db.execSQL("DELETE FROM surveys");
		db.execSQL("DELETE FROM items");
		db.execSQL("DELETE FROM cronjobs");
		db.close();
	}
	
	public void addDummy(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("INSERT INTO surveys(name) VALUES('Cats and Dogs');");
		db.close();
	}
	
	public void addDemoResultsTable(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("CREATE TABLE results1(id INTEGER PRIMARY KEY, q1_0 INT, q2_0 TEXT, q3_0 INT, q4_0 INT, q5_0 TEXT);");
		db.close();
	}

	public void addDemoSurvey(){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		values.put("prompt", "<html><body>Do you like cats?<br /><br /><input type='button' value='Yes' onclick='window.Surveyor.setResponse(\"0\")' /><br /><input type='button' value='No' onclick='window.Surveyor.setResponse(\"1\")' /></body></html>");
		values.put("id", 1);
		values.put("survey_id", 1);
		values.put("returns", "0");
		values.put("next", "2,0;3,1");
		db.insert("items", null, values);
		
		values.put("prompt", "<html><body>What is there to like about cats?<br /><br /><input type='text' id='ans' /><input type='button' value='Next' onclick='window.Surveyor.setResponse(document.getElementById(\"ans\").value)' /><script language='javascript'>document.getElementById(\"ans\").focus();</script></body></html>");
		values.put("id", 2);
		values.put("survey_id", 1);
		values.put("returns", "1");
		values.put("next", "5");
		db.insert("items", null, values);
		
		values.put("prompt", "<html><body>Do you like dogs?<br /><br /><input type='button' value='Yes' onclick='window.Surveyor.setResponse(\"0\")' /><br /><input type='button' value='No' onclick='window.Surveyor.setResponse(\"1\")' /></body></html>");
		values.put("id", 3);
		values.put("survey_id", 1);
		values.put("returns", "0");
		values.put("next", "5,0;4,1");
		db.insert("items", null, values);
		
		values.put("prompt", "<html><body>Do you like anything?<br /><br /><input type='button' value='Yes' onclick='window.Surveyor.setResponse(\"0\")' /><br /><input type='button' value='No' onclick='window.Surveyor.setResponse(\"1\")' /></body></html>");
		values.put("id", 4);
		values.put("survey_id", 1);
		values.put("returns", "0");
		values.put("next", "5");
		db.insert("items", null, values);
		
		values.put("prompt", "<html><body>How does this picture make you feel?<br /><br /><img src='garfield.png' /><br /><input type='text' id='ans' /><input type='button' value='Done' onclick='window.Surveyor.setResponse(document.getElementById(\"ans\").value)' /><script language='javascript'>document.getElementById(\"ans\").focus();</script></body></html>");
		values.put("id", 5);
		values.put("survey_id", 1);
		values.put("returns", "1");
		values.put("next", Item.SURVEY_END);
		db.insert("items", null, values);
		
		db.close();
	}
	
}
